-- 生成随机字符串
CREATE OR REPLACE FUNCTION rand_string(p_length INTEGER) RETURNS TEXT AS $$
    BEGIN
        RETURN SUBSTR(MD5(random()::text), 0, p_length + 1);
    END;
$$ LANGUAGE plpgsql;

-- 生成secret
CREATE OR REPLACE FUNCTION secret_string() RETURNS TEXT AS $$
    BEGIN
        RETURN MD5(rand_string(32));
    END;
$$ LANGUAGE plpgsql;

-- 生成密码
CREATE OR REPLACE FUNCTION encrypt_password(p_password VARCHAR, p_secret VARCHAR) RETURNS TEXT AS $$
    BEGIN
        RETURN MD5(p_password || '!ks#93a3JKSj2P&^%L=?' || p_secret);
    END;
$$ LANGUAGE plpgsql;

-- 生成后台管理员相关密码
CREATE OR REPLACE FUNCTION encrypt_password_admin(p_password VARCHAR, p_secret VARCHAR) RETURNS TEXT AS $$
    BEGIN
        RETURN MD5(p_password || ';k0#6,aK8%GK2N&^#)3^' || p_secret);
    END;
$$ LANGUAGE plpgsql;


-- 银行列表
CREATE TABLE banks (
    id SERIAL NOT NULL PRIMARY KEY,
    code CHAR(12) NOT NULL DEFAULT '',           -- 银行代码
    name VARCHAR(50) NOT NULL DEFAULT '',       -- 银行名称
    remark VARCHAR(100) NOT NULL DEFAULT '',    -- 备注
    status SMALLINT NOT NULL DEFAULT 1,         -- 状态, 0: 禁用; 1: 可用;
    sort INT NOT NULL DEFAULT 0                 -- 排序
);
-- 索引
CREATE UNIQUE INDEX ON banks(name);
CREATE UNIQUE INDEX ON banks(code);

-- 省份表
CREATE TABLE provinces (
    id SERIAL NOT NULL PRIMARY KEY,
    code CHAR(16) NOT NULL DEFAULT '',         -- 省份编码
    name VARCHAR(50) NOT NULL DEFAULT '',      -- 省份名称
    remark VARCHAR(50) NOT NULL DEFAULT '',    -- 备注
    sort INT NOT NULL DEFAULT 0
);
-- 索引
CREATE UNIQUE INDEX ON provinces(code);

-- 城市表
CREATE TABLE cities (
    id SERIAL NOT NULL PRIMARY KEY,
    province_id INT NOT NULL DEFAULT 0,         -- 省份编号
    province_code CHAR(16) NOT NULL DEFAULT '', -- 省份编码
    code CHAR(16) NOT NULL DEFAULT '',           -- 城市编号
    name VARCHAR(50) NOT NULL DEFAULT '',       -- 城市名称
    remark VARCHAR(50) NOT NULL DEFAULT '',     -- 备注
    sort INT NOT NULL DEFAULT 0
);
-- 索引
CREATE INDEX ON cities(province_id);
CREATE UNIQUE INDEX ON cities(code);

-- 县区表
-- CREATE TABLE districts (
--     district_code CHAR(16) NOT NULL DEFAULT '',         -- 县区编码
--     district_name VARCHAR(50) NOT NULL DEFAULT '',      -- 县区名称
--     district_remark VARCHAR(50) NOT NULL DEFAULT ''     -- 备注
-- ) INHERITS (cities);
-- -- 索引
-- CREATE UNIQUE INDEX ON districts(district_code);

-- 后台菜单表
CREATE TABLE menus (
    id SERIAL NOT NULL PRIMARY KEY,
    parent_id INT NOT NULL DEFAULT 0,       -- 上级菜单
    name VARCHAR(20) NOT NULL DEFAULT '',   -- 菜单名称
    url VARCHAR(100) NOT NULL DEFAULT '',   -- 链接地址
    icon VARCHAR(50) NOT NULL DEFAULT '',   -- 图标
    is_blank SMALLINT NOT NULL DEFAULT 0,        -- 是否外链
    is_show SMALLINT NOT NULL DEFAULT 1,         -- 是否显示
    sort INT NOT NULL DEFAULT 0,            -- 排序
    level SMALLINT NOT NULL DEFAULT 0       -- 菜单级别, 0: 一级菜单, 1: 二级菜单
);
-- 索引
CREATE INDEX ON menus(parent_id);

-- 管理员角色表
CREATE TABLE admin_roles (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(20) NOT NULL DEFAULT '',       -- 角色名称
    remark VARCHAR(100) NOT NULL DEFAULT '',    -- 备注
    status SMALLINT NOT NULL DEFAULT 0,         -- 状态
    menus JSON,                                 -- 权限菜单
    sort INT NOT NULL DEFAULT 0                 -- 排序
);
-- 索引
CREATE UNIQUE INDEX ON admin_roles(name);

-- 后台用户表
CREATE TABLE admins (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(20) NOT NULL DEFAULT '',           -- 管理员名称
    password CHAR(32) NOT NULL DEFAULT '',          -- 登录密码
    role_id INT NOT NULL DEFAULT 0,                 -- 角色编号
    secret CHAR(32) NOT NULL DEFAULT '',            -- 检验密码
    last_login TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- 最后登录时间
    login_count INT NOT NULL DEFAULT 0,             -- 登录次数
    last_ip INET,                                   -- 最后登录IP
    status SMALLINT NOT NULL DEFAULT 0,             -- 状态, 0: 停用; 1: 启用;
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- 添加时间
    updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- 修改时间
    sort INT NOT NULL DEFAULT 0,                    -- 排序
    remark VARCHAR(200) NOT NULL DEFAULT '',        -- 备注
    FOREIGN KEY (role_id) REFERENCES admin_roles(id)
);
-- 索引
CREATE UNIQUE INDEX ON admins(name);
-- 管理员登录
CREATE OR REPLACE FUNCTION admin_login(p_name VARCHAR, p_password VARCHAR, v_ip VARCHAR) RETURNS TEXT AS $$
    DECLARE
        r admins%ROWTYPE;
        v_real_password VARCHAR;
        v_secret VARCHAR;
        v_new_password VARCHAR;
    BEGIN
        SELECT * INTO r FROM admins WHERE name = p_name LIMIT 1;
        IF r IS NULL THEN  -- 如果没有找到到记录
            RETURN '后台用户名称或者登录密码有误';
        ELSE
            v_real_password = encrypt_password_admin(p_password, r.secret); -- 真正的密码
            IF v_real_password <> r.password THEN -- 如果密码不正确
                RETURN '后台用户名称或者用户密码错误';
            ELSEIF r.status = 0 THEN
                RETURN '用户账号已被停用';
            END IF;
        END IF;

        v_secret = secret_string();
        v_new_password = encrypt_password_admin(p_password, v_secret);
        UPDATE users
        SET
            secret = v_secret,              -- 新的密盐
            password = v_new_password,      -- 密码
            login_count = login_count + 1,  -- 登录次数
            last_login = NOW(),             -- 上次登录时间
            last_ip = v_ip::inet,           -- 上次登录ip
            updated = NOW()                 -- 更新时间
        WHERE id = r.id;
        RETURN 'ok';
    END;
$$ LANGUAGE plpgsql;
-- 生成初始密码
CREATE OR REPLACE FUNCTION admin_login_init() RETURNS TEXT AS $$
    DECLARE
        v_secret VARCHAR;
        v_password VARCHAR;
    BEGIN
        v_secret = secret_string();
        v_password = encrypt_password_admin('qwe123', v_secret);
        UPDATE admins
        SET
            secret = v_secret,
            password = v_password
        WHERE name = 'admin';
        RAISE NOTICE '原始密码: qwe123, Secret: %, Password: %', v_secret, v_password;
        RETURN v_secret;
    END;
$$ LANGUAGE plpgsql;

-- 管理员日志
CREATE TABLE admin_logs (
    id SERIAL NOT NULL,
    admin_id INT NOT NULL,                          -- 管理员编号
    admin_name VARCHAR(20) NOT NULL DEFAULT '',     -- 管理员名称
    log_type SMALLINT NOT NULL DEFAULT 0,           -- 日志类型, 0: 普通; 1: 财务; 2: 权限; 3: 系统
    url VARCHAR(200) NOT NULL DEFAULT '',           -- 操作URl
    remark VARCHAR(200) NOT NULL DEFAULT '',        -- 备注
    date DATE NOT NULL DEFAULT CURRENT_DATE,        -- 日期
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- 创建时间
    PRIMARY KEY(id, date),
    FOREIGN KEY (admin_id) REFERENCES admins(id)
) PARTITION BY RANGE (date);

-- 索引
CREATE INDEX ON admin_logs(date);
CREATE INDEX ON admin_logs(created);

-- 分区/按月
CREATE TABLE admin_logs_202005 PARTITION OF admin_logs FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE admin_logs_202006 PARTITION OF admin_logs FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE admin_logs_202007 PARTITION OF admin_logs FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE TABLE admin_logs_202008 PARTITION OF admin_logs FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');
CREATE TABLE admin_logs_202009 PARTITION OF admin_logs FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');
CREATE TABLE admin_logs_202010 PARTITION OF admin_logs FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE admin_logs_202011 PARTITION OF admin_logs FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE admin_logs_202012 PARTITION OF admin_logs FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE admin_logs_202101 PARTITION OF admin_logs FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE admin_logs_202102 PARTITION OF admin_logs FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE admin_logs_202103 PARTITION OF admin_logs FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE admin_logs_202104 PARTITION OF admin_logs FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE admin_logs_202105 PARTITION OF admin_logs FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE admin_logs_202106 PARTITION OF admin_logs FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE admin_logs_202107 PARTITION OF admin_logs FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE admin_logs_202108 PARTITION OF admin_logs FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE admin_logs_202109 PARTITION OF admin_logs FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE admin_logs_202110 PARTITION OF admin_logs FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE admin_logs_202111 PARTITION OF admin_logs FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE admin_logs_202112 PARTITION OF admin_logs FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');

-- 用户基本信息表
CREATE TABLE users (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(20) NOT NULL DEFAULT '',           -- 用户名称
    password CHAR(32) NOT NULL DEFAULT '',          -- 用户密码
    secret CHAR(32) NOT NULL DEFAULT '',            -- 安全密码
    status SMALLINT NOT NULL DEFAULT 1,             -- 状态, 0: 禁用; 1: 正常; 2: 锁定;
    last_login TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- 最后登录时间
    login_count INT NOT NULL DEFAULT 0,             -- 登录次数
    last_ip INET,                                   -- 最后登录IP
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- 注册时间
    updated TIMESTAMPTZ NOT NULL DEFAULT NOW()      -- 最后修改时间
) PARTITION BY HASH(id);

-- 创建索引
CREATE INDEX ON users(name);
CREATE INDEX ON users(status);

-- 创建分区
CREATE TABLE users_01 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 1);
CREATE TABLE users_02 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 2);
CREATE TABLE users_03 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 3);
CREATE TABLE users_04 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 4);
CREATE TABLE users_05 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 5);
CREATE TABLE users_06 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 6);
CREATE TABLE users_07 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 7);
CREATE TABLE users_08 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 8);
CREATE TABLE users_09 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 9);
CREATE TABLE users_10 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 10);
CREATE TABLE users_11 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 11);
CREATE TABLE users_12 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 12);
CREATE TABLE users_13 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 13);
CREATE TABLE users_14 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 14);
CREATE TABLE users_15 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 15);
CREATE TABLE users_16 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 0);

-- 触发器
-- 新用户注册时,初始化其账户信息、基本资料
CREATE OR REPLACE FUNCTION user_init_profile() RETURNS TRIGGER AS $$
    BEGIN
        -- 生成用户账户信息
        INSERT INTO user_accounts(user_id, user_name) VALUES (NEW.id, NEW.name);
        -- 生成用户基本信息
        INSERT INTO user_details(user_id, user_name) VALUES (NEW.id, NEW.name);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER user_init AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE user_init_profile();

-- 存储过程
-- 用户登录判断
CREATE OR REPLACE FUNCTION user_login(p_name VARCHAR, p_password VARCHAR, v_ip VARCHAR) RETURNS TEXT AS $$
    DECLARE
        r users%ROWTYPE;
        v_real_password VARCHAR;
        v_secret VARCHAR;
        v_new_password VARCHAR;
    BEGIN
        SELECT * INTO r FROM users WHERE name = p_name LIMIT 1;
        IF r IS NULL THEN  -- 如果没有找到到记录
            RETURN '用户名称或者登录密码有误';
        ELSE
            v_real_password = encrypt_password(p_password, r.secret); -- 真正的密码
            IF v_real_password <> r.password THEN -- 如果密码不正确
                RETURN '用户名称或者用户密码错误';
            ELSEIF r.status = 0 THEN
                RETURN '用户账号已被停用';
            END iF;
        END IF;

        v_secret = secret_string();
        v_new_password = encrypt_password(p_password, v_secret);
        UPDATE users
            SET
                secret = v_secret,              -- 新的密盐
                password = v_new_password,      -- 密码
                login_count = login_count + 1,  -- 登录次数
                last_login = NOW(),             -- 上次登录时间
                last_ip = v_ip::inet,           -- 上次登录ip
                updated = NOW()                 -- 更新时间
            WHERE id = r.id;
        RETURN 'ok';
    END;
$$ LANGUAGE plpgsql;
-- 生成初始密码
CREATE OR REPLACE FUNCTION user_login_init() RETURNS TEXT AS $$
    DECLARE
        v_secret VARCHAR;
        v_password VARCHAR;
    BEGIN
        v_secret = secret_string();
        v_password = encrypt_password('qwe123', v_secret);
        UPDATE users
            SET
                secret = v_secret,
                password = v_password
            WHERE name = 'temp_name1';
        RAISE NOTICE '原始密码: qwe123, Secret: %, Password: %', v_secret, v_password;
        RETURN v_secret || ' | ' || v_password;
    END;
$$ LANGUAGE plpgsql;

-- 用户账户表
CREATE TABLE user_accounts (
    id SERIAL NOT NULL,
    user_id INT NOT NULL,                           -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',      -- 用户名称
    frozen DECIMAL(16, 4) NOT NULL DEFAULT 0,       -- 现金冻结金额
    remain DECIMAL(16, 4) NOT NULL DEFAULT 0,       -- 现金可用余额
    total DECIMAL(16, 4) NOT NULL DEFAULT 0,        -- 现金可用总额
    total_in DECIMAL(16, 4) NOT NULL DEFAULT 0,     -- 现金入账总额
    total_out DECIMAL(16, 4) NOT NULL DEFAULT 0,    -- 现金出账总额
    FOREIGN KEY(user_id) REFERENCES users(id),
    PRIMARY KEY(id, user_id)
) PARTITION BY HASH(user_id);

-- 分区
CREATE TABLE user_accounts_01 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 1);
CREATE TABLE user_accounts_02 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 2);
CREATE TABLE user_accounts_03 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 3);
CREATE TABLE user_accounts_04 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 4);
CREATE TABLE user_accounts_05 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 5);
CREATE TABLE user_accounts_06 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 6);
CREATE TABLE user_accounts_07 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 7);
CREATE TABLE user_accounts_08 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 8);
CREATE TABLE user_accounts_09 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 9);
CREATE TABLE user_accounts_10 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 10);
CREATE TABLE user_accounts_11 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 11);
CREATE TABLE user_accounts_12 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 12);
CREATE TABLE user_accounts_13 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 13);
CREATE TABLE user_accounts_14 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 14);
CREATE TABLE user_accounts_15 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 15);
CREATE TABLE user_accounts_16 PARTITION OF user_accounts (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 0);

-- 用户详情表
CREATE TABLE user_details (
    id SERIAL NOT NULL,
    user_id INT NOT NULL,                       -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',  -- 用户名称
    birth DATE NOT NULL DEFAULT '1971-01-01',   -- 生日
    gender SMALLINT NOT NULL DEFAULT 2,         -- 性别
    phone VARCHAR(20) NOT NULL DEFAULT '',      -- 联系电话
    photo VARCHAR(200) NOT NULL DEFAULT '',     -- 头像
    mail VARCHAR(100) NOT NULL DEFAULT '',      -- 电子邮件
    address VARCHAR(100) NOT NULL DEFAULT '',   -- 联系地址
    remark VARCHAR(200) NOT NULL DEFAULT '',    -- 备注
    FOREIGN KEY(user_id) REFERENCES users(id),
    PRIMARY KEY(id, user_id)
) PARTITION BY HASH(user_id);

-- 表分区
CREATE TABLE user_details_01 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 1);
CREATE TABLE user_details_02 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 2);
CREATE TABLE user_details_03 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 3);
CREATE TABLE user_details_04 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 4);
CREATE TABLE user_details_05 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 5);
CREATE TABLE user_details_06 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 6);
CREATE TABLE user_details_07 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 7);
CREATE TABLE user_details_08 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 8);
CREATE TABLE user_details_09 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 9);
CREATE TABLE user_details_10 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 10);
CREATE TABLE user_details_11 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 11);
CREATE TABLE user_details_12 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 12);
CREATE TABLE user_details_13 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 13);
CREATE TABLE user_details_14 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 14);
CREATE TABLE user_details_15 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 15);
CREATE TABLE user_details_16 PARTITION OF user_details (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 0);

-- 用户绑卡列表
CREATE TABLE user_cards (
    id SERIAL NOT NULL,                             -- 编号
    user_id INT NOT NULL,                           -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',      -- 用户名称
    province_code CHAR(6) NOT NULL DEFAULT '',      -- 省份编码
    city_code CHAR(6) NOT NULL DEFAULT '',          -- 城市编码
    district_code CHAR(6) NOT NULL DEFAULT '',      -- 县区编码
    bank_id INT NOT NULL,                           -- 银行编号
    card_number VARCHAR(20) NOT NULL DEFAULT '',    -- 银行卡号
    real_name VARCHAR(20) NOT NULL DEFAULT '',      -- 真实姓名
    status SMALLINT NOT NULL DEFAULT 1,             -- 状态, 0: 禁用; 1: 可用;
    sort INT NOT NULL DEFAULT 0,                    -- 排序
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY (bank_id) REFERENCES banks(id),
    PRIMARY KEY(id, user_id)
) PARTITION BY HASH(user_id);

-- 分区
CREATE TABLE user_cards_01 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 1);
CREATE TABLE user_cards_02 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 2);
CREATE TABLE user_cards_03 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 3);
CREATE TABLE user_cards_04 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 4);
CREATE TABLE user_cards_05 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 5);
CREATE TABLE user_cards_06 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 6);
CREATE TABLE user_cards_07 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 7);
CREATE TABLE user_cards_08 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 8);
CREATE TABLE user_cards_09 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 9);
CREATE TABLE user_cards_10 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 10);
CREATE TABLE user_cards_11 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 11);
CREATE TABLE user_cards_12 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 12);
CREATE TABLE user_cards_13 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 13);
CREATE TABLE user_cards_14 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 14);
CREATE TABLE user_cards_15 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 15);
CREATE TABLE user_cards_16 PARTITION OF user_cards (user_id) FOR VALUES WITH (MODULUS 16, REMAINDER 0);

-- 账变记录表
CREATE TABLE account_changes (
    id SERIAL NOT NULL,
    bill_no CHAR(20) NOT NULL DEFAULT '',               -- 账变单号
    date DATE NOT NULL DEFAULT CURRENT_DATE,            -- 日期
    user_id INT NOT NULL,                               -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',          -- 用户名称
    amount DECIMAL(16, 4) NOT NULL DEFAULT 0,           -- 账变金额
    type  SMALLINT NOT NULL DEFAULT 0,                  -- 类型, 1: 充值; 2: 提现; 21: 提现拒绝;
    in_out SMALLINT NOT NULL DEFAULT 0,                 -- IO类型, 1: 入账; 2: 出账; 31: 平账冻结; 32: 平账回退
    before_remain DECIMAL(16, 4) NOT NULL DEFAULT 0,    -- 变前可用余额
    before_frozen DECIMAL(16, 4) NOT NULL DEFAULT 0,    -- 变前冻结金额
    before_total DECIMAL(16, 4) NOT NULL DEFAULT 0,     -- 变前总额
    after_remain DECIMAL(16, 4) NOT NULL DEFAULT 0,     -- 变后余额
    after_frozen DECIMAL(16, 4) NOT NULL DEFAULT 0,     -- 变后冻结金额
    after_total DECIMAL(16, 4) NOT NULL DEFAULT 0,      -- 变后部愘同
    remark VARCHAR(200) NOT NULL DEFAULT '',            -- 账变备注
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 账变时间
    FOREIGN KEY (user_id) REFERENCES users(id),
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (date);

-- 索引
CREATE INDEX ON account_changes(date);
CREATE INDEX ON account_changes(created);

-- 分区/按半月
CREATE TABLE account_changes_202005_01 PARTITION OF account_changes FOR VALUES FROM ('2020-05-01') TO ('2020-05-06');
CREATE TABLE account_changes_202005_02 PARTITION OF account_changes FOR VALUES FROM ('2020-05-06') TO ('2020-05-11');
CREATE TABLE account_changes_202005_03 PARTITION OF account_changes FOR VALUES FROM ('2020-05-11') TO ('2020-05-16');
CREATE TABLE account_changes_202005_04 PARTITION OF account_changes FOR VALUES FROM ('2020-05-16') TO ('2020-05-21');
CREATE TABLE account_changes_202005_05 PARTITION OF account_changes FOR VALUES FROM ('2020-05-21') TO ('2020-05-26');
CREATE TABLE account_changes_202005_06 PARTITION OF account_changes FOR VALUES FROM ('2020-05-26') TO ('2020-06-01');
CREATE TABLE account_changes_202006_01 PARTITION OF account_changes FOR VALUES FROM ('2020-06-01') TO ('2020-06-06');
CREATE TABLE account_changes_202006_02 PARTITION OF account_changes FOR VALUES FROM ('2020-06-06') TO ('2020-06-11');
CREATE TABLE account_changes_202006_03 PARTITION OF account_changes FOR VALUES FROM ('2020-06-11') TO ('2020-06-16');
CREATE TABLE account_changes_202006_04 PARTITION OF account_changes FOR VALUES FROM ('2020-06-16') TO ('2020-06-21');
CREATE TABLE account_changes_202006_05 PARTITION OF account_changes FOR VALUES FROM ('2020-06-21') TO ('2020-06-26');
CREATE TABLE account_changes_202006_06 PARTITION OF account_changes FOR VALUES FROM ('2020-06-26') TO ('2020-07-01');
CREATE TABLE account_changes_202007_01 PARTITION OF account_changes FOR VALUES FROM ('2020-07-01') TO ('2020-07-06');
CREATE TABLE account_changes_202007_02 PARTITION OF account_changes FOR VALUES FROM ('2020-07-06') TO ('2020-07-11');
CREATE TABLE account_changes_202007_03 PARTITION OF account_changes FOR VALUES FROM ('2020-07-11') TO ('2020-07-16');
CREATE TABLE account_changes_202007_04 PARTITION OF account_changes FOR VALUES FROM ('2020-07-16') TO ('2020-07-21');
CREATE TABLE account_changes_202007_05 PARTITION OF account_changes FOR VALUES FROM ('2020-07-21') TO ('2020-07-26');
CREATE TABLE account_changes_202007_06 PARTITION OF account_changes FOR VALUES FROM ('2020-07-26') TO ('2020-08-01');
CREATE TABLE account_changes_202008_01 PARTITION OF account_changes FOR VALUES FROM ('2020-08-01') TO ('2020-08-06');
CREATE TABLE account_changes_202008_02 PARTITION OF account_changes FOR VALUES FROM ('2020-08-06') TO ('2020-08-11');
CREATE TABLE account_changes_202008_03 PARTITION OF account_changes FOR VALUES FROM ('2020-08-11') TO ('2020-08-16');
CREATE TABLE account_changes_202008_04 PARTITION OF account_changes FOR VALUES FROM ('2020-08-16') TO ('2020-08-21');
CREATE TABLE account_changes_202008_05 PARTITION OF account_changes FOR VALUES FROM ('2020-08-21') TO ('2020-08-26');
CREATE TABLE account_changes_202008_06 PARTITION OF account_changes FOR VALUES FROM ('2020-08-26') TO ('2020-09-01');
CREATE TABLE account_changes_202009_01 PARTITION OF account_changes FOR VALUES FROM ('2020-09-01') TO ('2020-09-06');
CREATE TABLE account_changes_202009_02 PARTITION OF account_changes FOR VALUES FROM ('2020-09-06') TO ('2020-09-11');
CREATE TABLE account_changes_202009_03 PARTITION OF account_changes FOR VALUES FROM ('2020-09-11') TO ('2020-09-16');
CREATE TABLE account_changes_202009_04 PARTITION OF account_changes FOR VALUES FROM ('2020-09-16') TO ('2020-09-21');
CREATE TABLE account_changes_202009_05 PARTITION OF account_changes FOR VALUES FROM ('2020-09-21') TO ('2020-09-26');
CREATE TABLE account_changes_202009_06 PARTITION OF account_changes FOR VALUES FROM ('2020-09-26') TO ('2020-10-01');
CREATE TABLE account_changes_202010_01 PARTITION OF account_changes FOR VALUES FROM ('2020-10-01') TO ('2020-10-06');
CREATE TABLE account_changes_202010_02 PARTITION OF account_changes FOR VALUES FROM ('2020-10-06') TO ('2020-10-11');
CREATE TABLE account_changes_202010_03 PARTITION OF account_changes FOR VALUES FROM ('2020-10-11') TO ('2020-10-16');
CREATE TABLE account_changes_202010_04 PARTITION OF account_changes FOR VALUES FROM ('2020-10-16') TO ('2020-10-21');
CREATE TABLE account_changes_202010_05 PARTITION OF account_changes FOR VALUES FROM ('2020-10-21') TO ('2020-10-26');
CREATE TABLE account_changes_202010_06 PARTITION OF account_changes FOR VALUES FROM ('2020-10-26') TO ('2020-11-01');
CREATE TABLE account_changes_202011_01 PARTITION OF account_changes FOR VALUES FROM ('2020-11-01') TO ('2020-11-06');
CREATE TABLE account_changes_202011_02 PARTITION OF account_changes FOR VALUES FROM ('2020-11-06') TO ('2020-11-11');
CREATE TABLE account_changes_202011_03 PARTITION OF account_changes FOR VALUES FROM ('2020-11-11') TO ('2020-11-16');
CREATE TABLE account_changes_202011_04 PARTITION OF account_changes FOR VALUES FROM ('2020-11-16') TO ('2020-11-21');
CREATE TABLE account_changes_202011_05 PARTITION OF account_changes FOR VALUES FROM ('2020-11-21') TO ('2020-11-26');
CREATE TABLE account_changes_202011_06 PARTITION OF account_changes FOR VALUES FROM ('2020-11-26') TO ('2020-12-01');
CREATE TABLE account_changes_202012_01 PARTITION OF account_changes FOR VALUES FROM ('2020-12-01') TO ('2020-12-06');
CREATE TABLE account_changes_202012_02 PARTITION OF account_changes FOR VALUES FROM ('2020-12-06') TO ('2020-12-11');
CREATE TABLE account_changes_202012_03 PARTITION OF account_changes FOR VALUES FROM ('2020-12-11') TO ('2020-12-16');
CREATE TABLE account_changes_202012_04 PARTITION OF account_changes FOR VALUES FROM ('2020-12-16') TO ('2020-12-21');
CREATE TABLE account_changes_202012_05 PARTITION OF account_changes FOR VALUES FROM ('2020-12-21') TO ('2020-12-26');
CREATE TABLE account_changes_202012_06 PARTITION OF account_changes FOR VALUES FROM ('2020-12-26') TO ('2021-01-01');
CREATE TABLE account_changes_202101_01 PARTITION OF account_changes FOR VALUES FROM ('2021-01-01') TO ('2021-01-06');
CREATE TABLE account_changes_202101_02 PARTITION OF account_changes FOR VALUES FROM ('2021-01-06') TO ('2021-01-11');
CREATE TABLE account_changes_202101_03 PARTITION OF account_changes FOR VALUES FROM ('2021-01-11') TO ('2021-01-16');
CREATE TABLE account_changes_202101_04 PARTITION OF account_changes FOR VALUES FROM ('2021-01-16') TO ('2021-01-21');
CREATE TABLE account_changes_202101_05 PARTITION OF account_changes FOR VALUES FROM ('2021-01-21') TO ('2021-01-26');
CREATE TABLE account_changes_202101_06 PARTITION OF account_changes FOR VALUES FROM ('2021-01-26') TO ('2021-02-01');
CREATE TABLE account_changes_202102_01 PARTITION OF account_changes FOR VALUES FROM ('2021-02-01') TO ('2021-02-06');
CREATE TABLE account_changes_202102_02 PARTITION OF account_changes FOR VALUES FROM ('2021-02-06') TO ('2021-02-11');
CREATE TABLE account_changes_202102_03 PARTITION OF account_changes FOR VALUES FROM ('2021-02-11') TO ('2021-02-16');
CREATE TABLE account_changes_202102_04 PARTITION OF account_changes FOR VALUES FROM ('2021-02-16') TO ('2021-02-21');
CREATE TABLE account_changes_202102_05 PARTITION OF account_changes FOR VALUES FROM ('2021-02-21') TO ('2021-02-26');
CREATE TABLE account_changes_202102_06 PARTITION OF account_changes FOR VALUES FROM ('2021-02-26') TO ('2021-03-01');
CREATE TABLE account_changes_202103_01 PARTITION OF account_changes FOR VALUES FROM ('2021-03-01') TO ('2021-03-06');
CREATE TABLE account_changes_202103_02 PARTITION OF account_changes FOR VALUES FROM ('2021-03-06') TO ('2021-03-11');
CREATE TABLE account_changes_202103_03 PARTITION OF account_changes FOR VALUES FROM ('2021-03-11') TO ('2021-03-16');
CREATE TABLE account_changes_202103_04 PARTITION OF account_changes FOR VALUES FROM ('2021-03-16') TO ('2021-03-21');
CREATE TABLE account_changes_202103_05 PARTITION OF account_changes FOR VALUES FROM ('2021-03-21') TO ('2021-03-26');
CREATE TABLE account_changes_202103_06 PARTITION OF account_changes FOR VALUES FROM ('2021-03-26') TO ('2021-04-01');
CREATE TABLE account_changes_202104_01 PARTITION OF account_changes FOR VALUES FROM ('2021-04-01') TO ('2021-04-06');
CREATE TABLE account_changes_202104_02 PARTITION OF account_changes FOR VALUES FROM ('2021-04-06') TO ('2021-04-11');
CREATE TABLE account_changes_202104_03 PARTITION OF account_changes FOR VALUES FROM ('2021-04-11') TO ('2021-04-16');
CREATE TABLE account_changes_202104_04 PARTITION OF account_changes FOR VALUES FROM ('2021-04-16') TO ('2021-04-21');
CREATE TABLE account_changes_202104_05 PARTITION OF account_changes FOR VALUES FROM ('2021-04-21') TO ('2021-04-26');
CREATE TABLE account_changes_202104_06 PARTITION OF account_changes FOR VALUES FROM ('2021-04-26') TO ('2021-05-01');
CREATE TABLE account_changes_202105_01 PARTITION OF account_changes FOR VALUES FROM ('2021-05-01') TO ('2021-05-06');
CREATE TABLE account_changes_202105_02 PARTITION OF account_changes FOR VALUES FROM ('2021-05-06') TO ('2021-05-11');
CREATE TABLE account_changes_202105_03 PARTITION OF account_changes FOR VALUES FROM ('2021-05-11') TO ('2021-05-16');
CREATE TABLE account_changes_202105_04 PARTITION OF account_changes FOR VALUES FROM ('2021-05-16') TO ('2021-05-21');
CREATE TABLE account_changes_202105_05 PARTITION OF account_changes FOR VALUES FROM ('2021-05-21') TO ('2021-05-26');
CREATE TABLE account_changes_202105_06 PARTITION OF account_changes FOR VALUES FROM ('2021-05-26') TO ('2021-06-01');
CREATE TABLE account_changes_202106_01 PARTITION OF account_changes FOR VALUES FROM ('2021-06-01') TO ('2021-06-06');
CREATE TABLE account_changes_202106_02 PARTITION OF account_changes FOR VALUES FROM ('2021-06-06') TO ('2021-06-11');
CREATE TABLE account_changes_202106_03 PARTITION OF account_changes FOR VALUES FROM ('2021-06-11') TO ('2021-06-16');
CREATE TABLE account_changes_202106_04 PARTITION OF account_changes FOR VALUES FROM ('2021-06-16') TO ('2021-06-21');
CREATE TABLE account_changes_202106_05 PARTITION OF account_changes FOR VALUES FROM ('2021-06-21') TO ('2021-06-26');
CREATE TABLE account_changes_202106_06 PARTITION OF account_changes FOR VALUES FROM ('2021-06-26') TO ('2021-07-01');
CREATE TABLE account_changes_202107_01 PARTITION OF account_changes FOR VALUES FROM ('2021-07-01') TO ('2021-07-06');
CREATE TABLE account_changes_202107_02 PARTITION OF account_changes FOR VALUES FROM ('2021-07-06') TO ('2021-07-11');
CREATE TABLE account_changes_202107_03 PARTITION OF account_changes FOR VALUES FROM ('2021-07-11') TO ('2021-07-16');
CREATE TABLE account_changes_202107_04 PARTITION OF account_changes FOR VALUES FROM ('2021-07-16') TO ('2021-07-21');
CREATE TABLE account_changes_202107_05 PARTITION OF account_changes FOR VALUES FROM ('2021-07-21') TO ('2021-07-26');
CREATE TABLE account_changes_202107_06 PARTITION OF account_changes FOR VALUES FROM ('2021-07-26') TO ('2021-08-01');
CREATE TABLE account_changes_202108_01 PARTITION OF account_changes FOR VALUES FROM ('2021-08-01') TO ('2021-08-06');
CREATE TABLE account_changes_202108_02 PARTITION OF account_changes FOR VALUES FROM ('2021-08-06') TO ('2021-08-11');
CREATE TABLE account_changes_202108_03 PARTITION OF account_changes FOR VALUES FROM ('2021-08-11') TO ('2021-08-16');
CREATE TABLE account_changes_202108_04 PARTITION OF account_changes FOR VALUES FROM ('2021-08-16') TO ('2021-08-21');
CREATE TABLE account_changes_202108_05 PARTITION OF account_changes FOR VALUES FROM ('2021-08-21') TO ('2021-08-26');
CREATE TABLE account_changes_202108_06 PARTITION OF account_changes FOR VALUES FROM ('2021-08-26') TO ('2021-09-01');
CREATE TABLE account_changes_202109_01 PARTITION OF account_changes FOR VALUES FROM ('2021-09-01') TO ('2021-09-06');
CREATE TABLE account_changes_202109_02 PARTITION OF account_changes FOR VALUES FROM ('2021-09-06') TO ('2021-09-11');
CREATE TABLE account_changes_202109_03 PARTITION OF account_changes FOR VALUES FROM ('2021-09-11') TO ('2021-09-16');
CREATE TABLE account_changes_202109_04 PARTITION OF account_changes FOR VALUES FROM ('2021-09-16') TO ('2021-09-21');
CREATE TABLE account_changes_202109_05 PARTITION OF account_changes FOR VALUES FROM ('2021-09-21') TO ('2021-09-26');
CREATE TABLE account_changes_202109_06 PARTITION OF account_changes FOR VALUES FROM ('2021-09-26') TO ('2021-10-01');
CREATE TABLE account_changes_202110_01 PARTITION OF account_changes FOR VALUES FROM ('2021-10-01') TO ('2021-10-06');
CREATE TABLE account_changes_202110_02 PARTITION OF account_changes FOR VALUES FROM ('2021-10-06') TO ('2021-10-11');
CREATE TABLE account_changes_202110_03 PARTITION OF account_changes FOR VALUES FROM ('2021-10-11') TO ('2021-10-16');
CREATE TABLE account_changes_202110_04 PARTITION OF account_changes FOR VALUES FROM ('2021-10-16') TO ('2021-10-21');
CREATE TABLE account_changes_202110_05 PARTITION OF account_changes FOR VALUES FROM ('2021-10-21') TO ('2021-10-26');
CREATE TABLE account_changes_202110_06 PARTITION OF account_changes FOR VALUES FROM ('2021-10-26') TO ('2021-11-01');
CREATE TABLE account_changes_202111_01 PARTITION OF account_changes FOR VALUES FROM ('2021-11-01') TO ('2021-11-06');
CREATE TABLE account_changes_202111_02 PARTITION OF account_changes FOR VALUES FROM ('2021-11-06') TO ('2021-11-11');
CREATE TABLE account_changes_202111_03 PARTITION OF account_changes FOR VALUES FROM ('2021-11-11') TO ('2021-11-16');
CREATE TABLE account_changes_202111_04 PARTITION OF account_changes FOR VALUES FROM ('2021-11-16') TO ('2021-11-21');
CREATE TABLE account_changes_202111_05 PARTITION OF account_changes FOR VALUES FROM ('2021-11-21') TO ('2021-11-26');
CREATE TABLE account_changes_202111_06 PARTITION OF account_changes FOR VALUES FROM ('2021-11-26') TO ('2021-12-01');
CREATE TABLE account_changes_202112_01 PARTITION OF account_changes FOR VALUES FROM ('2021-12-01') TO ('2021-12-06');
CREATE TABLE account_changes_202112_02 PARTITION OF account_changes FOR VALUES FROM ('2021-12-06') TO ('2021-12-11');
CREATE TABLE account_changes_202112_03 PARTITION OF account_changes FOR VALUES FROM ('2021-12-11') TO ('2021-12-16');
CREATE TABLE account_changes_202112_04 PARTITION OF account_changes FOR VALUES FROM ('2021-12-16') TO ('2021-12-21');
CREATE TABLE account_changes_202112_05 PARTITION OF account_changes FOR VALUES FROM ('2021-12-21') TO ('2021-12-26');
CREATE TABLE account_changes_202112_06 PARTITION OF account_changes FOR VALUES FROM ('2021-12-26') TO ('2022-01-01');

-- 触发器
-- 保存账变
CREATE OR REPLACE FUNCTION account_change_save(p_bill_no VARCHAR, p_user_id INTEGER, p_type INTEGER, p_io INTEGER, p_amount DECIMAL) RETURNS VOID AS $$
    DECLARE
        v_date DATE;
        v_created TIMESTAMPTZ;
        v_r user_accounts%ROWTYPE;
        v_frozen DECIMAL(16, 4);
        v_remain DECIMAL(16, 4);
        v_total DECIMAL(16, 4);
    BEGIN
        v_date = CURRENT_DATE;
        v_created = NOW();

        SELECT * INTO v_r FROM user_accounts WHERE user_id = p_user_id; -- 先列出当前系统余额
        IF v_r IS NULL THEN
            RETURN;
        END IF;

        IF p_io = 1 THEN                        -- 入账
            v_frozen = v_r.frozen;              -- 冻结不变
            v_remain = v_r.remain + p_amount;   -- 余额增加
            v_total = v_r.total + p_amount;     -- 总额增加
        ELSIF p_io = 2 THEN                    -- 出账
            v_frozen = v_r.frozen - p_amount;   -- 冻结减少
            v_remain = v_r.v_remain;            -- 余额不变
            v_total = v_r.total - p_amount;     -- 总额减少
        ELSIF p_io = 31 THEN                   -- 平账: 冻结
            v_frozen = v_r.frozen + p_amount;   -- 冻结增加
            v_remain = v_r.remain - p_amount;   -- 可用减少
            v_total = v_r.total;                -- 总额不变
        ELSIF p_io = 32 THEN                   -- 平账: 回退
            v_frozen = v_r.frozen - p_amount;   -- 冻结减少
            v_remain = v_r.remain + p_amount;   -- 余额增加
            v_total = v_r.total;                -- 总额不变
        END IF;

        -- 写入账变记录
        INSERT INTO account_changes
            (bill_no, date, user_id, user_name, amount, type, in_out, before_remain, before_frozen, before_total, after_remain, after_frozen, after_total, created)
        VALUES
            (p_bill_no, v_date, p_user_id, v_r.user_name, p_amount, p_type, p_io, v_r.remain, v_r.frozen, v_r.total, v_remain, v_frozen, v_total, v_created);
    END;
$$ LANGUAGE plpgsql;

-- 充值记录
CREATE TABLE recharges (
    id SERIAL NOT NULL,
    bill_no CHAR(20) NOT NULL DEFAULT '',               -- 订单号
    date DATE NOT NULL DEFAULT CURRENT_DATE,            -- 日期
    user_id INT NOT NULL,                               -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',          -- 用户名称
    amount DECIMAL(16, 4) NOT NULL DEFAULT 0,           -- 支付金额
    is_first SMALLINT NOT NULL DEFAULT 0,               -- 是否首存
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 添加时间
    updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 修改时间
    status SMALLINT NOT NULL DEFAULT 0,                 -- 状态, 0: 待审核; 1: 成功; 2: 失败; 3: 冻结
    audit_admin_id INT NOT NULL DEFAULT 0,              -- 审核管理员编号
    audit_admin_name VARCHAR(20) NOT NULL DEFAULT '',   -- 审核管理员名称
    audit_remark VARCHAR(200) NOT NULL DEFAULT '',      -- 审核备注
    audited TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 审核时间
    pay_type SMALLINT NOT NULL DEFAULT 0,               -- 支付类型, 0: 网银转账; 1: 微信; 2: 支付宝
    pay_bank_id INT NOT NULL DEFAULT 0,                 -- 支付银行编号
    pay_receipt VARCHAR(50) NOT NULL DEFAULT 0,         -- 支付回执单号
    reason VARCHAR(200) NOT NULL DEFAULT '',            -- 拒绝原因
    finished TIMESTAMPTZ,                               -- 完成时间/成功时间
    FOREIGN KEY (user_id) REFERENCES users(id),
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (date);
-- 索引
CREATE INDEX ON recharges(date);        -- 日期
CREATE INDEX ON recharges(created);     -- 创建时间

-- 分区/按月
CREATE TABLE recharges_202005 PARTITION OF recharges FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE recharges_202006 PARTITION OF recharges FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE recharges_202007 PARTITION OF recharges FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE TABLE recharges_202008 PARTITION OF recharges FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');
CREATE TABLE recharges_202009 PARTITION OF recharges FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');
CREATE TABLE recharges_202010 PARTITION OF recharges FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE recharges_202011 PARTITION OF recharges FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE recharges_202012 PARTITION OF recharges FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE recharges_202101 PARTITION OF recharges FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE recharges_202102 PARTITION OF recharges FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE recharges_202103 PARTITION OF recharges FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE recharges_202104 PARTITION OF recharges FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE recharges_202105 PARTITION OF recharges FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE recharges_202106 PARTITION OF recharges FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE recharges_202107 PARTITION OF recharges FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE recharges_202108 PARTITION OF recharges FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE recharges_202109 PARTITION OF recharges FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE recharges_202110 PARTITION OF recharges FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE recharges_202111 PARTITION OF recharges FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE recharges_202112 PARTITION OF recharges FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');

--  触发器
--  修改时保存账变信息
CREATE OR REPLACE FUNCTION recharge_update_trigger() RETURNS TRIGGER AS $$
    DECLARE
        v_is_first INTEGER;
    BEGIN
        IF NEW.status = 0 THEN -- 不能修改为0
            RAISE EXCEPTION '修改充值状态的值不能为 0';
        ELSEIF NEW.status = 1 THEN -- 表示充值成功
            SELECT id FROM recharges WHERE user_id = NEW.user_id LIMIT 1;
            IF NOT FOUND THEN -- 表示是首存
                NEW.is_first = 1;
            END IF;
            EXECUTE account_change_save(NEW.bill_no, NEW.user_id, 1, 1, NEW.amount);
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER recharge_update AFTER UPDATE ON recharges FOR EACH ROW EXECUTE PROCEDURE recharge_update_trigger();

-- 保存信息时, 状态值必须为0
-- CREATE OR REPLACE FUNCTION recharge_create_trigger() RETURNS TRIGGER AS $$
--     BEGIN
--         -- 插入数据时此值必须为0
--         -- 如果状态值为2将导致账变出现误差
--         IF NEW.status <> 0 THEN
--             RAISE EXCEPTION '充值状态的值不能非0';
--         END IF;
--         SELECT id FROM recharges WHERE user_id = NEW.user_id LIMIT 1;
--         IF NOT FOUND THEN -- 是否是首次存款
--             NEW.is_first = 1;
--         END IF;
--         RETURN NEW;
--     END;
-- $$ LANGUAGE plpgsql;
-- CREATE TRIGGER recharge_create BEFORE INSERT ON recharges FOR EACH ROW EXECUTE PROCEDURE recharge_create_trigger();

-- 提现记录
CREATE TABLE withdraws (
    id SERIAL NOT NULL,
    bill_no CHAR(20) NOT NULL DEFAULT '',               -- 订单号
    date DATE NOT NULL DEFAULT CURRENT_DATE,            -- 日期
    user_id INT NOT NULL,                               -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',          -- 用户名称
    amount DECIMAL(16, 4) NOT NULL DEFAULT 0,           -- 支付金额
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 添加时间
    updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 修改时间
    status SMALLINT NOT NULL DEFAULT 0,                 -- 状态, 0: 待审核; 1: 已审待出款; 2: 已出款/提现成功; 3: 冻结; 4: 拒绝;
    audit_admin_id INT NOT NULL,                        -- 审核管理员编号
    audit_admin_name VARCHAR(20) NOT NULL DEFAULT '',   -- 审核管理员名称
    audit_remark VARCHAR(200) NOT NULL DEFAULT '',      -- 审核备注
    audited TIMESTAMPTZ NOT NULL DEFAULT NOW(),       -- 审核时间
    reason VARCHAR(200) NOT NULL DEFAULT '',            -- 拒绝原因
    finished TIMESTAMPTZ,                               -- 完成时间/成功时间
    bank_id INT NOT NULL DEFAULT 0,                     -- 银行编号
    bank_name VARCHAR(50) NOT NULL DEFAULT '',          -- 银行名称
    real_name VARCHAR(20) NOT NULL DEFAULT '',          -- 真实姓名
    card_id INT NOT NULL DEFAULT 0,                     -- 用户银行卡编号
    card_number VARCHAR(20) NOT NULL DEFAULT '',        -- 用户卡号
    fee DECIMAL(16, 4) NOT NULL DEFAULT 0,              -- 手续费
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (audit_admin_id) REFERENCES admins(id),
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (date);

-- 索引
CREATE INDEX ON withdraws(date);
CREATE INDEX ON withdraws(created);

-- 分区/按月
CREATE TABLE withdraws_202005 PARTITION OF withdraws FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE withdraws_202006 PARTITION OF withdraws FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE withdraws_202007 PARTITION OF withdraws FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE TABLE withdraws_202008 PARTITION OF withdraws FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');
CREATE TABLE withdraws_202009 PARTITION OF withdraws FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');
CREATE TABLE withdraws_202010 PARTITION OF withdraws FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE withdraws_202011 PARTITION OF withdraws FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE withdraws_202012 PARTITION OF withdraws FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE withdraws_202101 PARTITION OF withdraws FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE withdraws_202102 PARTITION OF withdraws FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE withdraws_202103 PARTITION OF withdraws FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE withdraws_202104 PARTITION OF withdraws FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE withdraws_202105 PARTITION OF withdraws FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE withdraws_202106 PARTITION OF withdraws FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE withdraws_202107 PARTITION OF withdraws FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE withdraws_202108 PARTITION OF withdraws FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE withdraws_202109 PARTITION OF withdraws FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE withdraws_202110 PARTITION OF withdraws FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE withdraws_202111 PARTITION OF withdraws FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE withdraws_202112 PARTITION OF withdraws FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');

--  触发器
--  修改时保存账变信息
CREATE OR REPLACE FUNCTION withdraw_update_trigger() RETURNS TRIGGER AS $$
    BEGIN
        IF NEW.status < OLD.status THEN -- 不能将状态进行回退
            RAISE EXCEPTION '修改充值状态的值不能为 0';
        ELSEIF NEW.status = 2 THEN      -- 表示出款成功
            EXECUTE account_change_save(NEW.bill_no, NEW.user_id, 2, 2, NEW.amount);
        ELSEIF NEW.status = 4 THEN      -- 拒绝出款
            EXECUTE account_change_save(NEW.bill_no, NEW.user_id, 21, 32, NEW.amount);
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER withdraw_update AFTER UPDATE ON withdraws FOR EACH ROW EXECUTE PROCEDURE withdraw_update_trigger();

-- 保存信息时, 状态值必须为0
-- CREATE OR REPLACE FUNCTION withdraw_create_trigger() RETURNS TRIGGER AS $$
--     BEGIN
--         -- 插入数据时此值必须为0
--         -- 如果状态值为其他将导致账变出现误差
--         IF NEW.status <> 0 THEN
--             RAISE EXCEPTION '充值状态的值不能非0';
--         END IF;
--         RETURN NEW;
--     END;
-- $$ LANGUAGE plpgsql;
-- CREATE TRIGGER withdraw_create BEFORE INSERT ON withdraws FOR EACH ROW EXECUTE PROCEDURE withdraw_create_trigger();

-- 申请提现
-- 执行此函数前, 需要保证用户有提款金额大于0
CREATE OR REPLACE FUNCTION apply_withdraw(p_bill_no VARCHAR, p_user_id INTEGER, p_amount DECIMAL, p_card_id INTEGER) RETURNS TEXT AS $$
    DECLARE
        v_r user_accounts%ROWTYPE;
        v_bank banks%ROWTYPE;
        v_card user_cards%ROWTYPE;
    BEGIN
        -- 先检测用户在不在
        SELECT * INTO v_r WHERE user_id = p_user_id;
        IF NOT FOUND THEN
            RETURN '用户信息查找失败';
        END IF;
        -- 检测用户账户信息
        IF v_r.remain < p_amount THEN
            RETURN '用户余额不足';
        END IF;
        -- 获取用户卡信息
        SELECT * INTO v_card FROM user_cards WHERE user_id = p_user_id AND id = p_card_id;
        IF NOT FOUND THEN
            RETURN '用户的绑定银行卡无效';
        END IF;
        -- 获取银行相关信息
        SELECT * INTO v_bank FROM banks WHERE id = v_card.bank_id;
        IF NOT FOUND THEN
            RETURN '用户银行卡的银行信息无效';
        END IF;
        -- 冻结用户相应提款金额
        UPDATE user_accounts
            SET
                frozen = frozen + p_amount,
                remain = remain - p_amout
            WHERE
                uesr_id = p_user_id;
        -- 写提款申请信息
        INSERT INTO withdraws
                (bill_no, date, user_id, user_name, amount, bank_id, bank_name, card_id, card_number, real_name)
            VALUES
                (p_bill_no, CURRENT_DATE, v_r.user_id, v_r.user_name, p_amount, v_bank.id, v_bank.name, p_card_id, v_card.card_number, v_card.real_name);
        RETURN 'ok';
    END;
$$ LANGUAGE plpgsql;


